const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

function addPBSFieldsToDevices() {
  const db = new Database(DB_PATH);

  try {
    console.log('检查设备表结构...');
    
    // 检查设备表是否存在
    const tableExists = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name='devices'
    `).get();

    if (!tableExists) {
      console.log('设备表不存在，跳过添加PBS字段');
      return;
    }

    // 检查是否已经有PBS相关字段
    const columnCheck = db.prepare(`
      PRAGMA table_info(devices)
    `).all();

    const hasFieldPBSLevelId = columnCheck.some(col => col.name === 'pbs_level_id');
    const hasFieldPBSInstanceId = columnCheck.some(col => col.name === 'pbs_instance_id');

    console.log('当前设备表字段:', columnCheck.map(col => col.name).join(', '));

    // 添加PBS层级ID字段
    if (!hasFieldPBSLevelId) {
      console.log('添加 pbs_level_id 字段到设备表...');
      db.exec(`
        ALTER TABLE devices 
        ADD COLUMN pbs_level_id INTEGER REFERENCES pbs_levels(id)
      `);
      console.log('✅ pbs_level_id 字段添加成功');
    } else {
      console.log('⚠️  pbs_level_id 字段已存在');
    }

    // 添加PBS实例ID字段
    if (!hasFieldPBSInstanceId) {
      console.log('添加 pbs_instance_id 字段到设备表...');
      db.exec(`
        ALTER TABLE devices 
        ADD COLUMN pbs_instance_id TEXT
      `);
      console.log('✅ pbs_instance_id 字段添加成功');
    } else {
      console.log('⚠️  pbs_instance_id 字段已存在');
    }

    // 创建索引
    const indexExists = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='index' AND name='idx_devices_pbs_level'
    `).get();

    if (!indexExists) {
      console.log('创建PBS相关索引...');
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_devices_pbs_level 
        ON devices(pbs_level_id)
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_devices_pbs_instance 
        ON devices(pbs_instance_id)
      `);
      console.log('✅ PBS索引创建成功');
    } else {
      console.log('⚠️  PBS索引已存在');
    }

    // 显示更新后的表结构
    const updatedColumns = db.prepare(`
      PRAGMA table_info(devices)
    `).all();

    console.log('\n📋 设备表结构更新完成：');
    updatedColumns.forEach(col => {
      const isPBSField = ['pbs_level_id', 'pbs_instance_id'].includes(col.name);
      console.log(`  ${isPBSField ? '🆕' : '  '} ${col.name} (${col.type})`);
    });

    // 检查设备表记录数量
    const deviceCount = db.prepare('SELECT COUNT(*) as count FROM devices').get();
    console.log(`\n📊 设备表当前有 ${deviceCount.count} 条记录`);

    console.log('\n✅ 设备表PBS字段添加完成！');
    console.log('\n📖 使用方法：');
    console.log('1. 新建设备时可以选择PBS层级');
    console.log('2. 选择层级后会自动生成 pbs_instance_id');
    console.log('3. 现有设备可以通过编辑添加PBS层级');

  } catch (error) {
    console.error('❌ 添加PBS字段失败:', error);
    throw error;
  } finally {
    db.close();
  }
}

// 同时检查和创建其他可能的实例表
function checkAndUpdateOtherInstanceTables() {
  const db = new Database(DB_PATH);

  try {
    console.log('\n🔍 检查其他实例表...');
    
    // 获取所有表名
    const tables = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name LIKE '%instance%'
      ORDER BY name
    `).all();

    console.log('发现的实例表:', tables.map(t => t.name).join(', '));

    // 为每个实例表添加PBS字段（如果需要）
    tables.forEach(table => {
      const tableName = table.name;
      
      try {
        const columns = db.prepare(`PRAGMA table_info(${tableName})`).all();
        const hasFieldPBSLevelId = columns.some(col => col.name === 'pbs_level_id');
        const hasFieldPBSInstanceId = columns.some(col => col.name === 'pbs_instance_id');

        if (!hasFieldPBSLevelId || !hasFieldPBSInstanceId) {
          console.log(`\n为表 ${tableName} 添加PBS字段...`);
          
          if (!hasFieldPBSLevelId) {
            db.exec(`
              ALTER TABLE ${tableName} 
              ADD COLUMN pbs_level_id INTEGER REFERENCES pbs_levels(id)
            `);
            console.log(`  ✅ ${tableName}.pbs_level_id 添加成功`);
          }
          
          if (!hasFieldPBSInstanceId) {
            db.exec(`
              ALTER TABLE ${tableName} 
              ADD COLUMN pbs_instance_id TEXT
            `);
            console.log(`  ✅ ${tableName}.pbs_instance_id 添加成功`);
          }

          // 添加索引
          db.exec(`
            CREATE INDEX IF NOT EXISTS idx_${tableName}_pbs_level 
            ON ${tableName}(pbs_level_id)
          `);
          db.exec(`
            CREATE INDEX IF NOT EXISTS idx_${tableName}_pbs_instance 
            ON ${tableName}(pbs_instance_id)
          `);
        }
      } catch (err) {
        console.warn(`  ⚠️  处理表 ${tableName} 时出错:`, err.message);
      }
    });

  } catch (error) {
    console.error('检查其他实例表失败:', error);
  } finally {
    db.close();
  }
}

async function main() {
  try {
    console.log('🚀 开始为设备表添加PBS层级字段...\n');
    
    // 1. 为设备表添加PBS字段
    addPBSFieldsToDevices();
    
    // 2. 检查其他实例表
    checkAndUpdateOtherInstanceTables();
    
    console.log('\n🎉 PBS字段添加完成！');
    
  } catch (error) {
    console.error('❌ 执行失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  main();
}

module.exports = {
  addPBSFieldsToDevices,
  checkAndUpdateOtherInstanceTables
};